package com.iszhangsc.backup.service.impl;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.date.LocalDateTimeUtil;
import cn.hutool.core.exceptions.ExceptionUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.io.IORuntimeException;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.RuntimeUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.core.util.ZipUtil;
import cn.hutool.system.SystemUtil;
import com.iszhangsc.backup.model.BackupRecord;
import com.iszhangsc.backup.param.dto.ReductionDTO;
import com.iszhangsc.backup.param.vo.DatabaseTableVO;
import com.iszhangsc.backup.repository.BackupRecordRepository;
import com.iszhangsc.backup.service.DatabaseBackupService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.CommandLineRunner;
import org.springframework.core.annotation.Order;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.StopWatch;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.RandomAccessFile;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * <p>
 * 数据库备份业务操作
 * </p>
 *
 * @author zhāngshìchāng
 * @date 2020/12/8 3:07 下午
 */
@Slf4j
@Service
@Order(1)
public class DatabaseBackupServiceImpl implements DatabaseBackupService, CommandLineRunner {

    private final JdbcTemplate jdbcTemplate;
    private final BackupRecordRepository backupRecordRepository;

    public DatabaseBackupServiceImpl(JdbcTemplate jdbcTemplate, BackupRecordRepository backupRecordRepository) {
        this.jdbcTemplate = jdbcTemplate;
        this.backupRecordRepository = backupRecordRepository;
    }

    // ========================= 数据库连接信息 ================================= //

    @Value("${spring.datasource.host}")
    private String host;
    @Value("${spring.datasource.port}")
    private Integer port;
    @Value("${spring.datasource.database-name}")
    private String databaseName;
    @Value("${spring.datasource.username}")
    private String username;
    @Value("${spring.datasource.password}")
    private String password;

    /**
     * 备份文件路径
     */
    @Value("${backup.path}")
    private String backupPath;


    /**
     * SQL文件后缀
     */
    private static final String SQL_SUFFIX = ".sql";
    /**
     * 路径分隔符.统一采用 "/" 可以跨操作系统支持.
     */
    private static final String PATH_SEPARATOR = "/";

    /**
     * 备份脚本命令,需要填充连接信息.
     */
    private String initialBackupCmd;

    /**
     * 还原脚本,索引为3的命令需要填充连接信息.
     */
    private final String[] initialRestoreCmd = new String[3];

    /**
     * todo 后续考虑怎么做.
     * 还原数据备份标识,控制接口不能调用(还原数据会删表、锁表,容易引发接口错误、接口响应时间很长).
     */
    private volatile boolean restoreFlag = false;


    @Override
    @SuppressWarnings("SpellCheckingInspection")
    public boolean doBackup() {
        // todo 后续添加前端传入的数据库进行备份


        StopWatch stopWatch = new StopWatch("数据备份任务");
        boolean success = true;
        // 找表名
        List<DatabaseTableVO> tables = this.tables(databaseName);

        InputStream inputStream = null;
        BackupRecord databaseBackup = new BackupRecord();
        List<File> needToMergeFiles = new ArrayList<>(tables.size());
        LocalDateTime now = LocalDateTime.now();
        // 创建存放备份文件的文件夹
        File zipDirectory = new File(backupPath + now.getYear() + "" + now.getMonthValue());
        if (!FileUtil.isDirectory(zipDirectory)) {
            FileUtil.mkdir(zipDirectory);
        }
        String dateTimeStr = LocalDateTimeUtil.format(now, "yyyyMMddHHmmss");
        // 这个目录用来存储临时表文件,后面用完需要删除
        File tableSqlTmpDirectory = FileUtil.mkdir(zipDirectory.getAbsolutePath() + PATH_SEPARATOR + dateTimeStr);
        try {
            //// todo 这里后面可以考虑多线程写文件
            for (DatabaseTableVO table : tables) {
                // 填充脚本的连接信息、备份数据库名、备份表名
                String script = StrUtil.format(initialBackupCmd, host, port, username, password, databaseName, table.getTableName());
                Process process = RuntimeUtil.exec(script);

                stopWatch.start("备份文件名: "+ table.getTableName() + SQL_SUFFIX + " 文件大小: " + FileUtil.readableFileSize(table.getDataLength()) + " 写入");
                inputStream = process.getInputStream();
                // 路径分隔符使用 '/' 、Linux正常使用并且可以兼容Windows
                File tableFile = new File(tableSqlTmpDirectory.getAbsolutePath() + PATH_SEPARATOR + table.getTableName() + SQL_SUFFIX);
                Files.copy(inputStream, tableFile.getAbsoluteFile().toPath());
                // 这里必须要往文件首行写入 "-- "(mysql注释), 否则还原要报错。
                // 因为使用mysqldump明文密码导出会在行首有一行警告:  mysqldump: [Warning] Using a password on the command line interface can be insecure.
                fastAppendNote(tableFile);
                needToMergeFiles.add(tableFile);
                stopWatch.stop();
            }

            stopWatch.start("合成ZIP");
            // 每张表单独备份,最终合成ZIP,减少磁盘占用.
            String backupFileName = dateTimeStr + ".zip";
            File realBackupFile = new File(zipDirectory.getAbsolutePath() + PATH_SEPARATOR + backupFileName);
            ZipUtil.zip(realBackupFile, StandardCharsets.UTF_8, false, needToMergeFiles.toArray(new File[0]));
            stopWatch.stop();

            stopWatch.start("备份记录保存");
            databaseBackup.setPath(realBackupFile.getAbsolutePath());
            String realBackupFileSize = FileUtil.readableFileSize(realBackupFile);
            String needToMergeFilesSize = FileUtil.readableFileSize(tables.stream().mapToLong(DatabaseTableVO::getDataLength).sum());
            log.info("本次备份文件路径:{},  备份源大小:{}, 备份压缩包大小:{}", realBackupFile.getAbsolutePath(), needToMergeFilesSize, realBackupFileSize);
            databaseBackup.setSize(realBackupFileSize);
            databaseBackup.setBackupMsg("备份成功");
        } catch (Exception e) {
            success = false;
            String stackTrace = ExceptionUtil.stacktraceToString(e);
            log.error("执行MySQL备份发生异常: {}", stackTrace);
            databaseBackup.setBackupMsg("备份失败: \n" + stackTrace);
        } finally {
            IoUtil.close(inputStream);
            FileUtil.del(tableSqlTmpDirectory);
        }
        this.backupRecordRepository.save(databaseBackup);
        stopWatch.stop();
        log.info(stopWatch.prettyPrint());
        return success;
    }


    @Override
    public boolean doReduction(ReductionDTO dto) {
        String path = dto.getPath();
        List<String> needReductionTables = dto.getTableNames();
        restoreFlag = true;
        boolean success = true;
        StopWatch stopWatch = new StopWatch("数据还原任务");
        stopWatch.start("解压还原文件: " + dto.getPath());
        File unzip = ZipUtil.unzip(new File(path), StandardCharsets.UTF_8);
        stopWatch.stop();

        File[] files = unzip.listFiles();
        if (files == null) {
            return false;
        }
        String restoreMsg;
        try {
            String mysqlScript = initialRestoreCmd[2];
            for (File file : files) {
                String fileName = file.getName();
                if (CollectionUtil.isNotEmpty(needReductionTables) && !needReductionTables.contains(fileName.replace(SQL_SUFFIX, ""))) {
                    log.info("跳过文件: {} 还原", fileName);
                    continue;
                }
                String absolutePath = file.getAbsolutePath();
                stopWatch.start("还原文件: " + absolutePath + "文件大小: " + FileUtil.readableFileSize(file.length()));
                String resolveMysqlScript = StrUtil.format(mysqlScript, host, port, username, password, databaseName, absolutePath);
                String[] restoreCmd = new String[3];
                ArrayUtil.copy(initialRestoreCmd, restoreCmd, 2);
                restoreCmd[2] = resolveMysqlScript;
                log.info("执行还原脚本命令:{}", Arrays.toString(restoreCmd));
                Process exec = RuntimeUtil.exec(restoreCmd);
                exec.waitFor();
                stopWatch.stop();
            }


            stopWatch.start("备份记录还原状态更新");
            restoreMsg = "还原成功";
        } catch (Exception e) {
            success = false;
            String stackTrace = ExceptionUtil.stacktraceToString(e);
            restoreMsg = "还原失败:\n" + stackTrace;
            log.error("执行MySQL备份还原发生异常:{}", stackTrace);
        } finally {
            restoreFlag = false;
        }

        String parent = files[0].getParent();
        FileUtil.del(parent);
        log.info("删除解压文件夹:{}", parent);
        this.jdbcTemplate.update("update backup_record set restore_msg = ? where path = ?", restoreMsg, path);
        stopWatch.stop();

        log.info(stopWatch.prettyPrint());
        return success;
    }

    @Override
    public List<DatabaseTableVO> tables(String tableSchema) {
        tableSchema = tableSchema == null ? this.databaseName : tableSchema;
        return jdbcTemplate.query("select table_name as tableName,table_comment as tableComment," +
                        "sum(data_length) as dataLength " +
                        "from information_schema.TABLES where table_schema = ? " +
                        "group by table_name, table_comment " +
                        "order by table_name", new Object[]{tableSchema},
                new BeanPropertyRowMapper<>(DatabaseTableVO.class));
    }

    /**
     * 判断数据库是否存在
     * @param dbName
     * @return
     */
    public boolean isDbExist(String dbName) {
        try {
            String sql = "USE "+dbName;
            jdbcTemplate.execute(sql);
            return true;
        } catch (Exception e) {
            System.out.println("数据库不存在");
            return false;
        }
    }
    public boolean isTableExist(String tableName) {
        Connection conn = null;
        ResultSet rs = null;
        try {
            conn = jdbcTemplate.getDataSource().getConnection();
            rs = null;
            DatabaseMetaData data = conn.getMetaData();
            String[] types = {"TABLE"};
            rs = data.getTables(null, null, tableName, types);
            if(rs.next()){
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            try {
                rs.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }


    @Override
    public boolean initdb() {
        if(!isTableExist("scheduled_cron")){
            StringBuffer sb=new StringBuffer();
            sb.append(" CREATE TABLE `scheduled_cron` (                        ");
            sb.append("   `id` int NOT NULL AUTO_INCREMENT,                    ");
            sb.append("   `code` varchar(255) DEFAULT NULL,                    ");
            sb.append("   `cron` varchar(255) DEFAULT NULL,                    ");
            sb.append("   `create_Time` datetime DEFAULT NULL,                 ");
            sb.append("   `update_Time` datetime DEFAULT NULL,                 ");
            sb.append("   PRIMARY KEY (`id`)                                   ");
            sb.append(" ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 ");
            jdbcTemplate.execute(sb.toString());
            sb=new StringBuffer();
            sb.append("INSERT INTO `scheduled_cron` VALUES (2,'databaseBackupScheduled','0 0 2 * * ?','2021-02-01 22:59:04','2021-02-01 22:59:04')");
            jdbcTemplate.execute(sb.toString());
        }

        if(!isTableExist("backup_record")){
            StringBuffer sb=new StringBuffer();
            sb.append(" CREATE TABLE `backup_record` (                                                         ");
            sb.append("   `path` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,         ");
            sb.append("   `size` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,         ");
            sb.append("   `backup_msg` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,   ");
            sb.append("   `id` int NOT NULL AUTO_INCREMENT,                                                    ");
            sb.append("   `backup_time` datetime DEFAULT NULL,                                                 ");
            sb.append("   `restore_msg` varchar(255) DEFAULT NULL,                                             ");
            sb.append("   `restore_time` datetime DEFAULT NULL,                                                ");
            sb.append("   PRIMARY KEY (`id`)                                                                   ");
            sb.append(" ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8                                  ");
            jdbcTemplate.execute(sb.toString());
        }
        return true;
    }

    @Override
    public Page<BackupRecord> page(Integer pageNum, Integer pageSize) {
        return this.backupRecordRepository.findAll(PageRequest.of(pageNum, pageSize, Sort.by(Sort.Order.desc("id"))));
    }


    /**
     * MySQL备份、还原命令初始化.
     *
     * @param args arg
     */
    @Override
    public void run(String... args) {

        // 还原备份起始命令
        restoreStartCommand();

        try {
            tryDockerMysql();
        } catch (IORuntimeException dockerException) {
            tryUniversalMysql();
        }


    }



    private void tryDockerMysql() {
        // 查看是否是docker环境....
        // 默认mysql容器名称为mysql5.7
        String dockerMysqlVersion = RuntimeUtil.execForStr("docker exec mysql5.7 mysql --version");
        initialBackupCmd = "docker exec -i mysql5.7 /usr/bin/mysqldump -h{} -p{} -u{} --password={} {} {}";
        initialRestoreCmd[2] = "docker exec -i mysql5.7 mysql -h{} -p{} -u{} -p{} {} < {}";
        log.info("docker mysql version---> {}", dockerMysqlVersion);
    }

    private void tryUniversalMysql() {
        try {
            log.info("服务器无Docker环境,尝试通用版本MySQL.");
            // 尝试通用版本MySQL(二进制). Windows系统下的MySQL必须要配置MySQL环境变量
            String universalMysqlVersion = RuntimeUtil.execForStr("mysql --version");
            initialBackupCmd = "mysqldump -h{} -p{} -u{} -p{} {} {}";
            initialRestoreCmd[2] = "mysql -h{} -p{} -u{} -p{} {} < {}";
            log.info("universal mysql version---> {}", universalMysqlVersion);
        } catch (IORuntimeException universal) {
            log.error("MySQL运行环境不支持");
            throw universal;
        }
    }


    private void restoreStartCommand() {
        if (SystemUtil.getOsInfo().isWindows()) {
            initialRestoreCmd[0] = "cmd.exe";
            initialRestoreCmd[1] = "/c";
        } else {
            initialRestoreCmd[0] = "/bin/sh";
            initialRestoreCmd[1] = "-c";
        }
    }


    /**
     * 使用注意：这个方法有BUG 会删除原来起始位置的内容.如果不在意原文件起始内容,则可以使用该方法.
     * 快速追加MySQL注释(-- )到文件起始位置.
     *
     * @param targetFile 目标文件
     */
    private static void fastAppendNote(File targetFile) throws IOException {
        // 打开一个随机访问文件流，按读写方式
        try (RandomAccessFile randomFile = new RandomAccessFile(targetFile, "rw")) {
            // 将指定内容写文件第一个位置指针头。
            randomFile.seek(0);
            // MySQL注释
            randomFile.writeBytes("-- ");
        }
    }



}
